package com.orange.modules.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.entity.expert.AuthExpert;

@Repository
public class AuthExpertRepository {

	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthExpertDao authExpertDao;
	
	/**
	 * 新增专家
	 * @param ae
	 * @throws SQLException
	 */
	public void insertAuthExpert(AuthExpert ae) throws SQLException{
		authExpertDao.save(ae);
	}
	
	/**
	 * 查询所有的专家(或者根据 pid查询)
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthExpert> queryAllExpert(Integer pageNumber,Integer pageSize , Map<String ,String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 t.p_id as pid,t.s_expert_code as expertcode,"
				+ " t.s_expert_name as expertname,t.s_title as title,"
				+ " t.s_post as post,t.s_field as field,t.s_officephone as officephone,"
				+ " t.s_phone as phone,t.s_email as email,t.s_workunit as workunit,"
				+ " t.s_create_time as createtime,t.s_academy as academy,t.s_department as department,"
				+ " t.s_sex as sex,t.s_birthday as birthday,t.s_degree as degree,t.s_subject as subject,"
				+ " t.s_city as city,t.s_password as password,t.s_speciality as speciality,t.s_parttime as parttime,"
				+ " t.s_fax as fax,t.s_type as type,t.s_status as status FROM t_auth_expert t WHERE 1=1");
		
		if (StringUtils.isNotBlank(map.get("expertname"))) {
			sql.append(" AND t.s_expert_name like '%" + map.get("expertname")+ "%'");
		}
		if (StringUtils.isNotBlank(map.get("expertcode"))) {
			sql.append(" AND t.s_expert_name = '" + map.get("expertcode")+"'");
		}
		if(StringUtils.isNotBlank(map.get("subject")))
			sql.append(" AND t.s_subject like '%"+map.get("subject")+"%'");
		if(StringUtils.isNotBlank(map.get("city")))
			sql.append(" AND t.s_city like '%"+map.get("city")+"%'");
		
		sql.append(" ORDER BY t.s_create_time DESC ");
		Page<AuthExpert> page = dbHelper.getPage(sql.toString(), AuthExpert.class, pageNumber, pageSize);
		return page;
	}
	
	/**
	 * 根据账号查询专家信息
	 * @param Account
	 * @return
	 * @throws SQLException
	 */
	public AuthExpert queryExpertByAccount(String account) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_expert_code as expertcode,"
				+ " t.s_expert_name as expertname,t.s_title as title,"
				+ " t.s_post as post,t.s_field as field,t.s_officephone as officephone,"
				+ " t.s_phone as phone,t.s_email as email,t.s_workunit as workunit,"
				+ " t.s_create_time as createtime,t.s_academy as academy,t.s_department as department,"
				+ " t.s_sex as sex,t.s_birthday as birthday,t.s_degree as degree,t.s_subject as subject,"
				+ " t.s_city as city,t.s_password as password,t.s_speciality as speciality,t.s_parttime as parttime,"
				+ " t.s_fax as fax,t.s_type as type,t.s_status as status FROM t_auth_expert t WHERE t.s_expert_code =?";
		AuthExpert ae  = dbHelper.getBean(sql, AuthExpert.class,account);
		return ae;
	}
	
	/**
	 * 根据pid查询数据
	 * @param Account
	 * @return
	 * @throws SQLException
	 */
	public AuthExpert queryExpertBypid(String pid) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_expert_code as expertcode,"
				+ "t.s_expert_name as expertname,t.s_title as title,"
				+ "t.s_post as post,t.s_field as field,t.s_officephone as officephone,"
				+ "t.s_phone as phone,t.s_email as email,t.s_workunit as workunit,"
				+ "t.s_create_time as createtime,t.s_academy as academy,t.s_department as department,"
				+ "t.s_sex as sex,t.s_birthday as birthday,t.s_degree as degree,t.s_subject as subject,"
				+ "t.s_city as city,t.s_password as password,t.s_speciality as speciality,t.s_parttime as parttime,"
				+ "t.s_fax as fax,t.s_type as type FROM t_auth_expert t WHERE 1=1";
		if(StringUtils.isNotBlank(pid) && pid!=null){
			sql+="AND t.p_id ='"+pid+"'";
		}
		AuthExpert ae  = dbHelper.getBean(sql, AuthExpert.class);
		return ae;
	}
	/**
	 * 根据姓名或学科或城市查询专家
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthExpert> queryExpertByInfoPage(Map<String,String>map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 t.p_id as pid,t.s_expert_code as expertcode,"
				+ "t.s_expert_name as expertname,t.s_title as title,"
				+ "t.s_post as post,t.s_field as field,t.s_officephone as officephone,"
				+ "t.s_phone as phone,t.s_email as email,t.s_workunit as workunit,"
				+ "t.s_create_time as createtime,t.s_academy as academy,t.s_department as department,"
				+ "t.s_sex as sex,t.s_birthday as birthday,t.s_degree as degree,t.s_subject as subject,"
				+ "t.s_city as city,t.s_password as password,t.s_speciality as speciality,t.s_parttime as parttime,"
				+ "t.s_fax as fax,t.s_type as type,t.s_status as status FROM t_auth_expert t WHERE 1=1");
		if(StringUtils.isNotBlank(map.get("name")))
			sql.append(" AND t.s_expert_name like '%"+map.get("name")+"%'");
		if(StringUtils.isNotBlank(map.get("subject")))
			sql.append(" AND t.s_subject like '%"+map.get("subject")+"%'");
		if(StringUtils.isNotBlank(map.get("city")))
			sql.append(" AND t.s_city like '%"+map.get("city")+"%'");
		sql.append(" ORDER BY t.s_create_time desc");
		Page<AuthExpert> page = dbHelper.getPage(sql.toString(), AuthExpert.class,
				Integer.valueOf(map.get("pageNumber")), Integer.valueOf(map.get("pageSize")),new Object[] {});
		return page;
	}
	
		
	/**
	 * 更新专家信息
	 */
	public void updateAuthExpert(AuthExpert ae) throws SQLException{
		authExpertDao.save(ae);
	}
	
	
	/**
	 * 删除专家（pid）
	 * @param pid
	 * @throws SQLException
	 */
	public void deleteAuthExpert(String pid) throws SQLException{
		authExpertDao.delete(pid);
	}
	
	
	/**
	 * 根据专家id 查询专家信息
	 * @param pid
	 * @return
	 */
	public AuthExpert queryAuthExpertInfoById(String pid){
		AuthExpert a=authExpertDao.findOne(pid);
		return a;
	}
	
	/**
	 * 批量保存专家信息
	 * @param list
	 */
	public void saveAuthExpertInfos(List<AuthExpert> list){
		authExpertDao.save(list);
	}
	
	/**
	 * 查询未绑定现场考察专家组的 所有专家信息
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthExpert> queryUnAuthExpertList() throws SQLException{
		String sql="SELECT t.p_id as pid,t.s_expert_code as expertcode,"
				+ "t.s_expert_name as expertname,t.s_title as title,"
				+ "t.s_post as post,t.s_field as field,t.s_officephone as officephone,"
				+ "t.s_phone as phone,t.s_email as email,t.s_workunit as workunit,"
				+ "t.s_create_time as createtime,t.s_academy as academy,t.s_department as department,"
				+ "t.s_sex as sex,t.s_birthday as birthday,t.s_degree as degree,t.s_subject as subject,"
				+ "t.s_city as city,t.s_password as password,t.s_speciality as speciality,t.s_parttime as parttime,"
				+ "t.s_fax as fax,t.s_type as type,t.s_status as status " +
				" FROM t_auth_expert t WHERE t.s_status='0' ";
		List<AuthExpert> ls=dbHelper.getBeanList(sql, AuthExpert.class);
		return ls;
	}
	/**
	 * 查询未绑定材料评审专家组的 所有专家信息
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthExpert> queryUnAuthExpertList_material() throws SQLException{
		String sql="SELECT t.p_id as pid,t.s_expert_code as expertcode," + 
				" t.s_expert_name as expertname,t.s_title as title," + 
				" t.s_post as post,t.s_field as field,t.s_officephone as officephone," + 
				" t.s_phone as phone,t.s_email as email,t.s_workunit as workunit,"+ 
				" t.s_create_time as createtime,t.s_academy as academy,t.s_department as department,"+ 
				" t.s_sex as sex,t.s_birthday as birthday,t.s_degree as degree,t.s_subject as subject,"+ 
				" t.s_city as city,t.s_password as password,t.s_speciality as speciality,t.s_parttime as parttime,"+ 
				" t.s_fax as fax,t.s_type as type,t.s_status as status ,t.s_statuss as statuss" +
				" FROM t_auth_expert t WHERE t.s_statuss='0' ";
		List<AuthExpert> ls=dbHelper.getBeanList(sql, AuthExpert.class);
		return ls;
	}
	/**
	 * 根据ecodeId查询数据
	 * @param Account
	 * @return
	 * @throws SQLException
	 */
	public AuthExpert queryExpertInfoByEcodeId(String ecodeId) throws SQLException{
		String sql = "SELECT t.p_id as pid,t.s_expert_code as expertcode,"
				+ "t.s_expert_name as expertname,t.s_title as title,"
				+ "t.s_post as post,t.s_field as field,t.s_officephone as officephone,"
				+ "t.s_phone as phone,t.s_email as email,t.s_workunit as workunit,"
				+ "t.s_create_time as createtime,t.s_academy as academy,t.s_department as department,"
				+ "t.s_sex as sex,t.s_birthday as birthday,t.s_degree as degree,t.s_subject as subject,"
				+ "t.s_city as city,t.s_password as password,t.s_speciality as speciality,t.s_parttime as parttime,"
				+ "t.s_fax as fax,t.s_type as type FROM t_auth_expert t WHERE t.s_expert_code = ?";
		AuthExpert ae  = dbHelper.getBean(sql, AuthExpert.class,ecodeId);
		return ae;
	}
}
